package no.ntnu.fp.net.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import no.ntnu.fp.net.co.NotificationType;
import no.ntnu.fp.server.Console;


public class DatabaseConnection implements CalendarDatabase {
	private Connection dbConnection;
	private final static String DB_NAME = "CalendarSystemDatabase";
	private boolean debugMode = false;
	
	public boolean CreateNewDatabase() {

		try {
			dbConnection = DriverManager.getConnection("jdbc:derby:"+DB_NAME+";create=true");
			dbConnection.clearWarnings();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	/**
	 * Permanently deletes the persistent database data on the HD
	 * @return true if it succeeded
	 */
	public boolean DeleteDatabase() {		
		CloseConnection();
		return deleteDir( new File(DB_NAME) );
	}
	
	/**
	 * Initialize the database connection
	 * urlString: "jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*"
	 */
	public boolean OpenDatabaseConnection() {
		//Open the derby connection
		try {			
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			dbConnection = DriverManager.getConnection("jdbc:derby:"+DB_NAME+";");
			return true;			
		} catch (SQLException e) {
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		return false;
	}

	public int updateSQL( String query ) throws SQLException {
		Statement sqlStatement;
    	
		if( debugMode ) Console.printConsole("Running SQL query: " + query);
    	
		sqlStatement = dbConnection.createStatement();
		return sqlStatement.executeUpdate(query);
	}
	
	public ResultSet executeSQL( String query ) throws SQLException {
		Statement sqlStatement;
		
    	if( debugMode ) Console.printConsole("Running SQL query: " + query);

		sqlStatement = dbConnection.createStatement();
		return sqlStatement.executeQuery(query);		
	}

	public void CloseConnection() {					
		try {
			if( dbConnection.isClosed() ) return;
			dbConnection.commit();
			dbConnection.close();
			ShutdownDerby();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void ShutdownDerby(){
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			//do nothing, derby will always put out an exception if we succeeded
		}
	}
	
	/**
	 * This function is used to parse a SQL script and run each SQL statement line by line
	 * @param fileName name of the file to parse
	 */
	public boolean runSQLScript( String fileName )
	{
		Console.printConsole("Parsing SQL script: " + fileName);    	

        try
        {
    		InputStream in = new FileInputStream(fileName);
            Scanner s = new Scanner(in);
            Statement st = dbConnection.createStatement();
            s.useDelimiter("(;(\r)?\n)|(--\n)");
            
            //Keep reading and parsing
            while (s.hasNext())
            {
                String line = s.next();
                if (line.startsWith("/*!") && line.endsWith("*/"))
                {
                    int i = line.indexOf(' ');
                    line = line.substring(i + 1, line.length() - " */".length());
                }

                if (line.trim().length() > 0)
                {
                	if( debugMode ) Console.printConsole("EXECUTING SQL QUERY:\n" + line);
                    st.execute(line);
                }
            }
        } catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return false;
		}
        
		//All good! finished running the script
        return true;
	}
	
    // Deletes all files and subdirectories under dir.
    // Returns true if all deletions were successful.
    // If a deletion fails, the method stops attempting to delete and returns false.
    private boolean deleteDir(File dir) {
    	Console.printConsole("Deleting " + dir.getAbsolutePath());
        if (dir.isDirectory()) {
            String[] children = dir.list();
            for (int i=0; i < children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    return false;
                }
            }
        }
    
        // The directory is now empty so delete it
        return dir.delete();
    }
    
    /**
     * Checks if the database has an open connection
     * @return return true if there is an open connnection and no errors happened
     */
    public boolean isOpen(){
    	try {
			return !dbConnection.isClosed();
		} catch (SQLException e) {
			//nothing
		}
		return false;
    }
    
    public boolean debugMode(){
    	debugMode = !debugMode;
    	return debugMode;
    }
    
    /**
     * Checks if the specified username with the password exists in the database. If it finds the person
     * then it returns the users full name, null otherwise.
     */
	public String confirmLoginData(String username, String passwordHash) {
		String query = "SELECT Password, Name FROM bruker WHERE username='"+username+"'";
		try {
			ResultSet result = executeSQL(query);
			while( result.next() ){
				//Make sure the password hashes matches
				if( result.getString("password").hashCode() == Integer.parseInt(passwordHash) )
					return result.getString("name");
			}
		} catch (SQLException e) {
			//None
			e.printStackTrace();
		}
		return "";
	}

	/**
	 * Inserts a new user with the specified username, name and password
	 * Returns number of rows affected
	 */
	public String CreateNewUser(String username, String name, String password)
			throws SQLException {
		String query = "INSERT into BRUKER VALUES( " +username+", " + name + ", " + password + " )";
		
		return Integer.toString( updateSQL(query) );
	}

	/**
	 * Returns all appointments at the specified month for the specified owner
	 * @param MSG_GET_APPOINTMENTS;id;owner;month;year;
	 * @return AppointmentID;Day;Month;Year;FromTime;ToTime;Description;MeetingRoomID;Owner;
	 */
	public String getAppointments(String username) {
			String query = "SELECT a.AppointmentID, a.Owner, a.Day, a.month, a.jahre, a.FromTime, a.ToTime, a.Description, a.MeetingRoomID FROM Appointment a, Participation p "
				+ " WHERE p.bruker = '" + username +"'"
				+ " AND p.appointmentID = a.appointmentID";
						
			String answer = "";
			try {
				ResultSet result = executeSQL(query);
				while( result.next() ) {
					answer += result.getInt("AppointmentID") + ";";
					answer += result.getInt("Day") + ";";
					answer += result.getInt("Month") + ";";
					answer += result.getInt("Jahre") + ";";
					answer += result.getInt("FromTime") + ";";
					answer += result.getInt("ToTime") + ";";
					answer += result.getString("Description") + ";";
					answer += result.getInt("MeetingRoomID") + ";";
					answer += result.getString("Owner") + ";";
				}
			} catch (SQLException e) {
				Console.printError("Error in getAppointments() - " + e);
				return "";
			}
			
			//132;5;10;2;2011;0325;0400;dette er min description;12;jojo;
			return answer;
	}

	/**
	 * @param MSG_ADD_APPOINTMENTS;owner;description;room;day;month;year;fromTime;toTime;participants[];
	 * @return the AppointmentID of the new appointment
	 */
	public String addAppointment(String owner, String description, String room, String day, String month, 
			String year, String fromTime, String toTime, String[] participants) throws SQLException {
		
		//First create the new appointment into the database
		String query = "INSERT INTO Appointment VALUES( DEFAULT, ";
		query += "'" + owner +"'" + ",";
		query += year + ",";
		query += month + ",";
		query += day + ",";
		query += fromTime + ",";
		query += toTime + ",";
		query += "'" + description + "'" + ",";
		query += room;
		query += " )";
		updateSQL(query);

		//Now figure out what ID the appointment has
		query = "SELECT AppointmentID FROM Appointment WHERE owner = '"+owner+"'";
		query += " AND Jahre = " + year;
		query += " AND Month = " + month;
		query += " AND Day = " + day;
		query += " AND fromTime = " + fromTime;
		query += " AND toTime = " + toTime;
		query += " AND MeetingRoomID = " + room;
		ResultSet result = executeSQL(query);
		result.next();
		String id = Integer.toString(result.getInt("AppointmentID"));

		//Add everyone to the participation list of the appointment
		for( String user : participants ) {
			query = "INSERT INTO Participation VALUES( '" + user + "', " + id + " )";
			updateSQL(query);
		}
		
		//Notify everyone affected
		notifyAffected(id, NotificationType.NOTIFY_NEW_APPOINTMENT);
		
		return id;		
	}

	/**
	 * Gets a list of all users in the database. Retrieves username and full name
	 */
	public String getAllUsers() {
		String query = "SELECT username, name FROM BRUKER";
		try {
			String result = "";
			ResultSet resultList;
			resultList = executeSQL(query);
			while( resultList.next() ){
				result = result.concat(resultList.getString("username") + ";" + resultList.getString("name") + ";");
			}
			
			//jojo;Johan Jansen;tore;Torstein Remming;zand;Sander Zeestrat;
			return result;
		} catch (SQLException e) {
			return "";
		}
	}
	
	public String getParticipants(String appointmentID) {
		String query = "SELECT Bruker FROM Participation WHERE AppointmentID = " + appointmentID;
		try {
			String result = "";
			ResultSet resultList;
			resultList = executeSQL(query);
			while (resultList.next()) {
				result = result.concat(resultList.getString("Bruker") +  ";");
			}
			return result;
		} catch (SQLException e) {
			return "";
		}
	}

	private void notifyAffected( String appointmentID, NotificationType status ) throws SQLException {
		String query;
		ResultSet result;
		int notifyID;
		
		//Create notification
		query = "INSERT INTO Notification VALUES( DEFAULT, " + appointmentID + " )";
		updateSQL(query);
		
		//Get the our notfication ID
		query = "SELECT MAX(NotificationID) as \"LastID\" FROM Notification WHERE AppointmentID = " + appointmentID;
		result = executeSQL(query);
		result.next();
		notifyID = result.getInt("LastID");
		result.close();

		//Notify all affected users
		query = "SELECT Bruker FROM Participation WHERE AppointmentID = " + appointmentID;
		result = executeSQL(query);
		while( result.next() )
		{
			String username = result.getString("Bruker");
			query = "INSERT INTO Recipient VALUES( '" + username + "', " + notifyID + ", '" + status.name() + "' )";
			updateSQL(query);
		}
	}
	
	/**
	 * Removes the specified appointment from the database. Needs security access
	 */
	public String cancelAppointment(String owner, String password, String id) throws SQLException {
		String query;
		int result = 0;
		
		//First make sure the user actually has access to editing this appointment
		if( confirmLoginData(owner, password).isEmpty() ) return "0";

		//Notify everyone affected
		notifyAffected(id, NotificationType.NOTIFY_DELETED_APPOINTMENT);

		//free the meeting room
		query = "UPDATE Appointment SET MeetingRoomID = null WHERE AppointmentID = " + id;
		result += updateSQL(query);

		//Now delete it!
		query = "DELETE FROM Participation WHERE AppointmentID = " + id;
		result += updateSQL(query);
		
		return Integer.toString(result);
	}
	
	public String getFreeMeetingRooms( String day, String month, String year, String fromTime, String toTime ) throws SQLException{
		String query = "SELECT RoomID FROM MeetingRoom WHERE RoomID NOT IN (SELECT m.RoomID FROM MeetingRoom m, Appointment a WHERE a.meetingroomid = m.RoomID";
		query += " AND a.jahre = " + year + " and a.month = " + month + " AND a.day  = " + day;
		query += " AND ((a.fromTime <= " + fromTime + " AND a.toTime >= " + fromTime;
		query += ") OR ( a.fromTime <= " + toTime + " AND a.toTime >= " + toTime + ")))";
		
		//Get all free meeting rooms
		ResultSet result = executeSQL(query);
		String answer = "";
		while( result.next() ) {
			answer += result.getInt("RoomID") + ";";
		}

		return answer;
	}

	public String getAllMeetingRooms() throws SQLException {
		ResultSet result = executeSQL("SELECT * FROM MeetingRoom");
		String answer = "";
		while( result.next() ){
			answer += result.getInt("RoomID") + ";";
			answer += result.getString("Name") + ";";
			answer += result.getInt("Capacity") + ";";
		}
		
		//5;Det lille rommet p� loftet;20;
		return answer;
	}

	/**
	 * Gets all notifications for the specified user
	 * @param username MSG_GET_ALL_NOTIFICATIONS;username;
	 * @return NotificationID;Type;AppointmentID;
	 */
	public String getNotifications(String username) {
		String query = "SELECT n.NotificationID, r.Type, a.AppointmentID FROM Recipient r, Notification n, Appointment a WHERE r.Bruker = '" + username +"' AND r.notificationID = n.notificationID AND n.appointmentID = a.appointmentID";
		String answer = "";
		try {
			ResultSet result = executeSQL(query);
			while(result.next()){
				answer += result.getString("NotificationID") + ";";
				answer += result.getString("Type") + ";";
				answer += result.getString("AppointmentID") + ";";
			}
		} catch (SQLException e) {
			//Do nothing
		}
		
		//5;0;4;
		return answer;
	}

	public String getAppointmentReplies(String appointmentID) {
		String query = "SELECT r.bruker, r.type FROM Notification n, Appointment a, Recipient r, Bruker b WHERE a.AppointmentID = " + appointmentID + " AND a.AppointmentID = n.AppointmentID AND n.NotificationID = r.NotificationID";
		String answer = "";
		try {
			ResultSet result = executeSQL(query);
			while(result.next()){
				answer += result.getString("Bruker") + ":";
				answer += result.getString("Type") + ";";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			// Do nothing
		}
		//kris;NOTIFY_NEW_MEETING;
		return answer;
	}

	public String removeUserFromAppointment(String owner, String password,
			String appointmentID, String username) throws SQLException  {
		//First make sure the user actually has access to editing this appointment
		if( confirmLoginData(owner, password).isEmpty() ) return "0";
		
		String query = "DELETE FROM Participation p WHERE p.AppointmentID = " + appointmentID +" AND p.bruker = '" + username + "'";
		
		return String.valueOf(updateSQL(query));
		
	}
	
	public String setNotificationStatus(String id, String status, String username) throws SQLException {
		String query = "UPDATE Recipient SET Type = '" + status + "' WHERE NotificationID = " + id + " AND bruker = '" + username + "'";
		return String.valueOf(updateSQL(query));	
	}
}
